package dao;

import pojo.Tcm;
import util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author lzj
 *
 */
public class TcmDao {
    private Connection conn = null;
    private PreparedStatement pstm = null;
    private ResultSet rs = null;



//展示
    public List<Tcm> getTcmList(int currentPage,int pageSize){
        List<Tcm> tcmList = new ArrayList<>();
        try{
            conn  = DbUtil.getConnection();
            String sql = "select * from tcm limit "+pageSize*(currentPage-1)+","+pageSize;
            pstm = conn.prepareStatement(sql);

            rs = pstm.executeQuery(sql);
            while(rs.next()){
                Tcm tcm = new Tcm();
                tcm.setId(rs.getInt("id"));
                tcm.setNumber(rs.getInt("number"));
                tcm.setName(rs.getString("name"));
                tcm.setAlias(rs.getString("alias"));
                tcm.setType(rs.getInt("type"));
                tcmList.add(tcm);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        System.out.println(tcmList.get(0).toString());
        return tcmList;
    }

//    关键字查询
    public List<Tcm> getQueryTcmList(String key,int flag){
        List<Tcm> tcmList = new ArrayList<>();
        String sql=null;
//        0名称查询
//        1别名查询
//        2原文查询
        switch (flag){
            case 0:{
                sql="select * from tcm where name like '%"+key+"%'";
                break;
            }
            case 1:{
                sql="select * from tcm where alias like '%"+key+"%'";
                break;
            }
            case 2:{
                sql="select * from tcm where bjyw like '%"+key+"%'";
                break;
            }
            default:{
                System.out.println("未知错误");
                break;
            }
        }
        try{
            System.out.println(sql);
            conn  = DbUtil.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                Tcm tcm = new Tcm();
                tcm.setId(rs.getInt("id"));
                tcm.setNumber(rs.getInt("number"));
                tcm.setName(rs.getString("name"));
                tcm.setAlias(rs.getString("alias"));
                tcm.setType(rs.getInt("type"));
                tcmList.add(tcm);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return tcmList;
    }

//获取中药具体信息Byid
    public Tcm getDetail(int id) {
        Tcm tcm = new Tcm();
        try{
            conn  = DbUtil.getConnection();
            String sql = "select * from tcm where id =?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while(rs.next()){
                tcm.setId(rs.getInt("id"));
                tcm.setNumber(rs.getInt("number"));
                tcm.setBjyw(rs.getString("bjyw"));
                tcm.setAlias(rs.getString("alias"));
                tcm.setChandi(rs.getString("chandi"));
                tcm.setDosage(rs.getString("dosage"));
                tcm.setName(rs.getString("name"));
                tcm.setNote(rs.getString("note"));
                tcm.setProcessing(rs.getString("processing"));
                tcm.setTaboo(rs.getString("taboo"));
                tcm.setType(rs.getInt("type"));
                tcm.setXingwei(rs.getString("xingwei"));
                tcm.setZhuzhi(rs.getString("zhuzhi"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return tcm;
    }

//    修改中草药信息
    public void updateTcm(Tcm tcm){
            System.out.println("in update");
        try{
            conn = DbUtil.getConnection();
            String sql = "update tcm set number=?,name=?,alias=?,type=?,bjyw=?,zhuzhi=?" +
                    ",xingwei=?,chandi=?,dosage=?,taboo=?,processing=?,note=? where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,tcm.getNumber());
            pstm.setString(2,tcm.getName());
            pstm.setString(3,tcm.getAlias());
            pstm.setInt(4,tcm.getType());
            pstm.setString(5, tcm.getBjyw());
            pstm.setString(6, tcm.getZhuzhi());
            pstm.setString(7, tcm.getXingwei());
            pstm.setString(8, tcm.getChandi());
            pstm.setString(9, tcm.getDosage());
            pstm.setString(10, tcm.getTaboo());
            pstm.setString(11, tcm.getProcessing());
            pstm.setString(12, tcm.getNote());
            pstm.setInt(13, tcm.getId());
            pstm.executeUpdate();
            System.out.println("out update");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, null);
        }
    }

//增加中药信息
    public void addTcm(Tcm tcm){
        conn  = DbUtil.getConnection();
        String sql = "insert into tcm(number,name,alias,type,bjyw,zhuzhi,xingwei," +
                "chandi,dosage,taboo,processing,note)values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try{
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,tcm.getNumber());
            pstm.setString(2,tcm.getName());
            pstm.setString(3,tcm.getAlias());
            pstm.setInt(4,tcm.getType());
            pstm.setString(5, tcm.getBjyw());
            pstm.setString(6, tcm.getZhuzhi());
            pstm.setString(7, tcm.getXingwei());
            pstm.setString(8, tcm.getChandi());
            pstm.setString(9, tcm.getDosage());
            pstm.setString(10, tcm.getTaboo());
            pstm.setString(11, tcm.getProcessing());
            pstm.setString(12, tcm.getNote());
            pstm.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
    }

//    删除
public void deleteTcm(int id){
    try{
        conn  = DbUtil.getConnection();
        String sql = "delete from tcm where id =?";
        pstm = conn.prepareStatement(sql);
        pstm.setInt(1, id);
        pstm.executeUpdate();
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        DbUtil.close(conn, pstm, null);
    }
}

public int getTotalNum(){
        int totalNum=0;
    try{
        conn  = DbUtil.getConnection();
        String sql = "select count(*) as totalnum from tcm";
        pstm = conn.prepareStatement(sql);
        rs = pstm.executeQuery(sql);
        while(rs.next()){
            totalNum=rs.getInt("totalnum");
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        DbUtil.close(conn, pstm, rs);
    }
    return totalNum;
}
}
